package ups.Database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Enumeration;

public class Database {
	Connection _connection;
	String TABLENAME = "ship_locations";
	public Database( String hostName, int portNum, String databaseName, String username, String password ) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql://" + hostName + ":" + portNum + "/" + databaseName;
			//System.out.println( url.toString() );
			_connection = DriverManager.getConnection(url, username, password);
		} catch ( Exception e ) {
			e.printStackTrace();
		}

	}
	
	public Connection getConnection() {
		return _connection;
	}
	
	public ResultSet execute( String query ) throws SQLException {
		System.out.println( query );
		Statement stmt = getConnection().createStatement();
		return stmt.executeQuery( query );
	}
	
	/* Primitive Method for Reading from Database 
	Takes the form of "SELECT * from $tables" */
	public ResultSet get( ArrayList<String> tableNames ) {
		try {
			Statement query = _connection.createStatement();
			StringBuffer queryString = new StringBuffer("SELECT * from ");
			
			queryString.append( tableNames.get( 0 ) );
			int tableNameIdx = 1;
			while( tableNameIdx < tableNames.size() ) {
				queryString.append( ", " + tableNames.get(tableNameIdx));
				tableNameIdx++;
			}
			
			return query.executeQuery( queryString.toString() );
		} catch( SQLException e ) {
			e.printStackTrace();
			System.exit(-1);
		}
		
		return null;
	}
	
	/* So you don't always need an ArrayList */
	public ResultSet get( String tableName ) {
		try {
			Statement query = _connection.createStatement();
			return query.executeQuery( "SELECT * from " + tableName );
		} catch ( SQLException e ) {
			e.printStackTrace();
			System.exit(-1);
		}
		
		return null;
	}
	
	/* Get, limited by date time, with a key of the right index */
	public ResultSet getFromDay( String tableName, Date date, String dateColumnName ) {
		try {
			Statement query = _connection.createStatement();
			Calendar cal = Calendar.getInstance();
			cal.setTime( date );
			cal.add( Calendar.DATE, 1 );
			Date nextDay = cal.getTime();
			String queryString = "SELECT * from " + tableName + 
			" WHERE " + dateColumnName + " BETWEEN '" + getDateTimeFormat().format( date ) + "' AND '" + getDateTimeFormat().format( nextDay ) + "';";
			System.out.println( queryString );
			return query.executeQuery( queryString );
		} catch( SQLException e ) {
			System.out.println( "Failed get for day" +  e.getMessage() );
			e.printStackTrace();
		}
		return null;
	}

	
	
	/* Primitive Method for Writing to the Database
	Takes the form of "INSERT into $table ($keys) VALUES ($values)" */
	public int put( String tableName, Hashtable<String, String> values) {
		try {
			StringBuffer insertString = new StringBuffer("INSERT into " + tableName + " (" );
			
			/* Need to order these keys */
			ArrayList<String> keys = new ArrayList<String>();
			
		    for (Enumeration<String> e = values.keys() ; e.hasMoreElements() ;) {
				keys.add( e.nextElement() );
			}
			
			insertString.append( "" + keys.get( 0 ) + "");
			int keyIdx = 1;
			while( keyIdx < keys.size() ) {
				insertString.append( ", " + keys.get( keyIdx ) + "");
				keyIdx++;
			}
			
			insertString.append( " )" );
			insertString.append(" VALUES (");
			
			insertString.append( "'" + values.get( keys.get( 0 )) + "'");
			
			keyIdx = 1;
			while( keyIdx < keys.size() ) {
				insertString.append( ", '" + values.get( keys.get( keyIdx )) + "'");
				keyIdx++;
			}
			
			insertString.append(")");
			
			Statement insertStmt = _connection.createStatement();
			
			System.out.println("Executing SQL: " + insertString.toString() );
			insertStmt.execute( insertString.toString() , Statement.RETURN_GENERATED_KEYS );
			
			ResultSet rs = insertStmt.getGeneratedKeys();
			rs.next();
			return  Integer.parseInt( rs.getString(1));
			
		} catch( SQLException e ) {
			e.printStackTrace();
			System.exit(-1);
		}
		
		return 0;
	}
	
	
	
	/* Convenience Date Formatter For SQL Dates */
	private SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss" );
	public SimpleDateFormat getDateTimeFormat() {
		return dateTimeFormat;
	}
	
	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	public SimpleDateFormat getDateFormat() {
		return dateFormat;
	}
	

	private SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
	public SimpleDateFormat getTimeFormat() {
		return timeFormat;
	}
	
	public void delete( String tableName ) throws SQLException {
		Statement query = _connection.createStatement();
		String deleteCommand = "DELETE FROM " + tableName;
		query.execute( deleteCommand );
	}

	public boolean tableExists( String tableName ) {
		return false;
	}
}
